<?php session_start();

	define("DB_SERVER", "localhost");
	define("DB_USER", "flatshare");
	define("DB_PASS", "flatPWD1");
	define("DB_NAME", "flatshare");
	
	$connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
	mysql_select_db(DB_NAME, $connection) or die(mysql_error());
	
	// code checks if user has elected to logout, returns them to the index.php if so
	if (isset($_GET['logout'])) {
		unset($_SESSION['username']);
		header('Location: index.php');
	}
	
	// code to check user logins, make sure only authorized people are actually logged in for every page
	if (!isset($_SESSION['username'])){
		if (isset($_POST['username']))
		{
			$username = $_POST['username'];
			$password = $_POST['password'];
			
			if (!checkUserPass($username, $password)) {
				$_SESSION['username'] = $username;
			}
		}
	}
	
	
	/*******************************************************************************
	*																			   *
	*	Contained below are all of the PHP functions required for various aspects  *
	*	of the Flatshare site.													   *
	*																			   *
	*******************************************************************************/
	
	
	// function to return the total for a given bill id
	function getBillTotal ($billid) {
		global $connection;
		
		$q = "SELECT Bill_Total FROM bill_info WHERE Bill_ID='$billid'";
		
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		return $dbarray[0];
	}
	
	// function to allow user to change their password
	function setPassword ($password) {
		global $connection;
		
		$password = md5($password);
		$username = $_SESSION['username'];
		
		$q = "UPDATE users SET Password='$password' WHERE Username='$username'";
		
		return mysql_query($q, $connection);
	}
	
	// function that returns a list of bills entered by a user
	function getBillIDS($username, $type) {
		global $connection;
		
		$q = "SELECT Bill_ID, Bill_Name, Bill_Type, Bill_Total FROM bill_info WHERE Username='$username' AND Bill_Type='$type'";
		
		return mysql_query($q, $connection);
	}
	
	// function that returns a list of users for the given bill id, listing if they are paid etc, shares maybe
	function getBillUsers($billid) {
		global $connection;
		
		$q = "SELECT Username, Bill_Share, Bill_Paid, Payment_Confirmed FROM bill_users WHERE Bill_ID='$billid' ORDER BY Bill_Paid ASC";
		
		return mysql_query($q, $connection);
	}
	
	// function that returns a list of users from the chore_users table for the given bill id
	function getChoreUsers($billid) {
		global $connection;
		
		$q = "SELECT Username, Chore_Completed FROM chore_users WHERE Bill_ID='$billid' ORDER BY Chore_Completed ASC";
		
		return mysql_query($q, $connection);

	}
	
	// function to confirm a payment
	function confirmPayment($billid, $username) {
		global $connection;
		
		$q = "UPDATE bill_users SET Payment_Confirmed=CURDATE() WHERE Bill_ID='$billid' AND Username='$username'";
		
		return mysql_query($q, $connection);
	}
	
	// function to return pending notifications
	function getNotifications($username) {
		global $connection;
		
		$q = "SELECT bill_info.Bill_ID, bill_users.Username, Bill_Name, F_Name, L_Name, Bill_Share, Bill_Paid FROM bill_info, bill_users, users WHERE bill_users.Username=users.Username AND bill_info.Username='$username' AND bill_info.Bill_ID=bill_users.Bill_ID AND Bill_Paid IS NOT NULL AND Payment_Confirmed IS NULL GROUP BY bill_info.Bill_ID";
		
		return mysql_query($q, $connection);
	}
	
	// function to check if there are pending flatshare group notifications
	function checkNotifications($username) {
		global $connection;
		
		$q = "SELECT COUNT(bill_info.Bill_ID) FROM bill_info, bill_users WHERE bill_info.Username='$username' AND bill_info.Bill_ID=bill_users.Bill_ID AND Bill_Paid IS NOT NULL AND Payment_Confirmed IS NULL";
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0;
		}
		
		$dbarray = mysql_fetch_array($result);
		return $dbarray[0];
	}
	
	// function for user to mark chore as completed
	function setChoreCompleted ($billid, $username) {
		global $connection;
		
		$q = "UPDATE chore_users SET Chore_Completed=CURDATE() WHERE Bill_ID='$billid' AND Username='$username'";
		
		return mysql_query($q, $connection);
	}
	
	// function for user to mark bill as paid
	function setBillPaid ($billid, $username) {
		global $connection;
		
		$q = "UPDATE bill_users SET Bill_Paid=CURDATE() WHERE Bill_ID='$billid' AND Username='$username'";
		
		return mysql_query($q, $connection);
	}
	
	// function to return table containing any bills for given username
	function getBills ($username) {
		global $connection;
		
		$q = "SELECT bill_info.Bill_ID, Bill_Name, Bill_Share, Bill_Due, Bill_Paid, Payment_Confirmed FROM bill_info, bill_users WHERE bill_users.Username='$username' AND bill_info.Bill_ID=bill_users.Bill_ID";
		
		return mysql_query($q, $connection);
	}
	
	// function to return table containing any chores for the given username
	function getChores ($username) {
		global $connection;
		
		$q = "SELECT bill_info.Bill_ID, Bill_Name, Bill_Due, Chore_Completed FROM bill_info, chore_users WHERE chore_users.Username='$username' AND bill_info.Bill_ID=chore_users.Bill_ID";
		
		return mysql_query($q, $connection);
	}
	
	// function to add a chore to the database
	function addChore ($billid, $username) {
		global $connection;
		
		$q = "INSERT INTO chore_users (Bill_ID, Username) VALUES ('$billid', '$username')";
		
		return mysql_query($q, $connection);
	}
	
	// function to add a a bill SHARE to the database
	function addShare ($billid, $username, $billshare) {
		global $connection;
		
		$q = "INSERT INTO bill_users (Bill_ID, Username, Bill_Share) VALUES ('$billid', '$username', '$billshare')";
		return mysql_query($q, $connection);
	}
	
	// function to add a bill/chore to the database
	function createBill ($billname, $billtype, $billtotal, $billdue) {
		global $connection;
		
		$username = $_SESSION['username'];
		
		list($d, $m, $y) = explode('/', $billdue);
		$mk = mktime(0, 0, 0, $m, $d, $y);
		$billdue = strftime('%Y-%m-%d',$mk);
		
		$q = "INSERT INTO bill_info (Username, Bill_Name, Bill_Type, Bill_Total, Bill_Due) VALUES ('$username', '$billname', '$billtype', '$billtotal', '$billdue')";
		$result = mysql_query($q, $connection);
		
		if (!$result) {
			return 0;
		}
		
		$q = "SELECT MAX(Bill_ID) FROM bill_info";
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		
		return $dbarray[0];
	}
	
	// function to return number of users in a group
	function getNumUsers ($groupid) {
		global $connection;
		
		$q = "SELECT COUNT(Username) FROM group_members WHERE Group_ID='$groupid'";
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		
		return $dbarray[0];
	}
	
	// function to make supplied user a group admin
	function setUserAdmin ($username) {
		global $connection;
		
		$q = "UPDATE group_members SET Admin='Y' WHERE Username='$username'";
		return mysql_query($q, $connection);
	}
	
	// function to remove a user from the group
	// only requires username as user can only belong to one group at a time
	function removeUser ($username) {
		global $connection;
		
		$q = "DELETE FROM group_members WHERE Username='$username'";
		return mysql_query($q, $connection);
	}
	
	// function to return array of username/firstname/lastname/email of all non-admin members of a group
	function getMembers ($groupid) {
		global $connection;
		
		$q = "SELECT group_members.Username, F_Name, L_Name, Email, Admin FROM users, group_members WHERE users.Username=group_members.Username AND group_members.Group_ID='$groupid'";
		
		return mysql_query($q, $connection);
	}
	
	// function to remove all group invites for a specified user
	function removeAllInvites($username) {
		global $connection;
		
		$q = "DELETE FROM group_invites WHERE Username='$username'";
		
		return mysql_query($q, $connection);
	}
	
	// function to remove one group invite
	function removeInvite($username, $groupid) {
		global $connection;
		
		$q = "DELETE FROM group_invites WHERE Username='$username' AND Group_ID='$groupid'";
		
		return mysql_query($q, $connection);
	}
	
	// function returns the adress details associated with a specific groupid
	function getAddress($groupid) {
		global $connection;
		
		$q = "SELECT Unit_No, Street_No, Street_Name, Street_Type, Suburb, Postcode FROM groups WHERE Group_ID='$groupid'";
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		
		return $dbarray;
	}
	
	// function returns firstname,lastname of username provided
	function getUserName($username) {
		global $connection;
		
		$q = "SELECT F_Name, L_Name FROM users WHERE Username='$username'";
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		
		return $dbarray;
	}
	
	// function to get an array of pending flatshare group invitations
	// actually returns a resource, page uses resource to get array data
	function getInvites($username) {
		global $connection;
		
		$q = "SELECT Group_ID, Invited_By FROM group_invites WHERE Username='$username'";
		
		return mysql_query($q, $connection);
	}
	
	// function to check if there are pending flatshare group invitations
	function checkInvites($username) {
		global $connection;
		
		$q = "SELECT COUNT(Username) FROM group_invites WHERE Username='$username'";
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0;
		}
		
		$dbarray = mysql_fetch_array($result);
		return $dbarray[0];
	}
	
	// function to add a user to the pending group invite table group_invites
	function inviteUser($groupid, $username, $invitedby) {
		global $connection;
		
		$q = "INSERT INTO group_invites (Username, Group_ID, Invited_By) VALUES ('$username', '$groupid', '$invitedby')";
		return mysql_query($q, $connection);
	}
	
	// function to check if username is in the database
	function checkUser($username) {
		global $connection;
		
		$q = "SELECT Username FROM users WHERE Username='$username'";
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0; // username not found
		}
		
		return 1; // valid username
	}
	
	// function searches for a user by their email address, returns username
	function findUserFromEmail($email) {
		global $connection;
		
		$q = "SELECT Username FROM users WHERE Email='$email'";
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0; // no user found
			
		}
		
		$dbarray = mysql_fetch_array($result);
		return $dbarray['Username'];
	}
	
	// function to remove user from the group
	// if user is only member of group, removes the flatshare group as well
	function leaveGroup($username) {
		global $connection;
		$groupid = checkUserGroup($username);
		$removegroup = 0;
		
		if (checkOnlyGroupMember($groupid)) {
			$removegroup = 1;
		}
		
		$q = "DELETE FROM group_members WHERE Username='$username'";
		mysql_query($q, $connection);
		
		if ($removegroup) {
			$q = "DELETE FROM group_invites WHERE Group_ID='$groupid'";
			mysql_query($q, $connection);
			$q = "DELETE FROM groups WHERE Group_ID='$groupid'";
			mysql_query($q, $connection);
		}
	}
	
	// function checks if it is "valid" for the user to leave the group
	// invalid to leave if multiple users in group, and user leaving is the only admin user
	function checkLeaveGroup($username) {
		global $connection;
		$groupid = checkUserGroup($username);
		
		if (checkOnlyGroupMember($groupid)) {
			return 1;
		}
		else {
			if (checkGroupAdmin($groupid, $username)) {
				if (checkMultiGroupAdmins($groupid)) {
					return 1;
				}
				else {
					return 0;
				}
			}
			else {
				return 1;
			}
		}
		
		return 0; // indicates NOT ok to leave group
	}
	
	// function to check if only user left in flatshare group
	function checkOnlyGroupMember($groupid) {
		global $connection;
		
		$q = "SELECT COUNT(Username) FROM group_members WHERE Group_ID='$groupid'";
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		
		if ($dbarray[0] > 1) {
			return 0; // more than 1 user
		}
		return 1; // only 1 user
	}
	
	// function to check if user is admin in flatshare group
	function checkGroupAdmin($groupid, $username) {
		global $connection;
		
		$q = "SELECT Admin FROM group_members WHERE Group_ID='$groupid' AND Username='$username' AND Admin='Y'";
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0; // user is not admin
		}
		return 1; // user is admin
	}
	
	// function to check if there are multiple admins in a flatshare group
	function checkMultiGroupAdmins($groupid) {
		global $connection;
		
		$q = "SELECT COUNT(Admin) FROM group_members WHERE Group_ID='$groupid' AND Admin='Y'";
		$result = mysql_query($q, $connection);
		$dbarray = mysql_fetch_array($result);
		
		if ($dbarray[0] >= 2) {
			return 1;
		}
		
		return 0;
	}
	
	// function checks if the username supplied is a member of a flatshare group
	function checkUserGroup($username) {
		global $connection;
		
		$q = "SELECT Group_ID FROM group_members WHERE username='$username'";
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0;
		}
		
		$dbarray = mysql_fetch_array($result);
		return $dbarray['Group_ID'];
	}
	
	// function adds a new flatshare group to the database
	function addNewGroup($unitno, $streetno, $streetname, $streettype, $suburb, $postcode) {
		global $connection;
		
		$q = "INSERT INTO groups (Unit_No, Street_No, Street_Name, Street_Type, Suburb, Postcode) VALUES ('$unitno', '$streetno', '$streetname', '$streettype', '$suburb', '$postcode')";
		
		return mysql_query($q, $connection);
	}
	
	// function to get the group ID of a flatshare group
	// can be used to check if the database already contains an address
	function checkGroupAddress($unitno, $streetno, $streetname, $streettype, $suburb, $postcode) {
		global $connection;
		
		$q = "SELECT Group_ID FROM groups WHERE Unit_No='$unitno' AND Street_No='$streetno' AND Street_Name='$streetname' AND Street_Type='$streettype' AND Suburb='$suburb' AND Postcode='$postcode'";
		
		$result = mysql_query($q, $connection);
		
		if (!$result || (mysql_numrows($result) < 1)) {
			return 0; // indicates address not in database
		}
		
		$dbarray = mysql_fetch_array($result);
		
		return $dbarray['Group_ID'];
	}
	
	// function to add user to a flatshare group
	function addGroupUser($groupid, $username, $admin) {
		global $connection;
		
		$q = "INSERT INTO group_members (Username, Group_ID, Admin, Date_Joined) VALUES ('$username', '$groupid', '$admin', CURDATE())";
		
		return mysql_query($q, $connection);
	}
	
	// function to insert a new user into the database, as all data checked with javascript at the form stage, this will 
	// return an error if the username is already in use
	function addNewUser($username, $password1, $fname, $lname, $dob, $title, $email, $mobno, $homeno, $workno) {
		global $connection;
		$password1 = md5($password1);
		
		// change date into correct format for mysql date storage:  dd/mm/yyyy -> yyyy-mm-dd
		list($d, $m, $y) = explode('/', $dob);
		$mk = mktime(0, 0, 0, $m, $d, $y);
		$dob = strftime('%Y-%m-%d',$mk);
		
		
		$q = "INSERT INTO users VALUES ('$username', '$password1', '$fname', '$lname', '$dob', '$title', '$email', '$mobno', '$homeno', '$workno')";
		return mysql_query($q, $connection);
	}
	
	// function to check whether the username/password are correct
	function checkUserPass($username, $password) {
		global $connection;
			  
		$username = str_replace("'","''",$username);
		$password = md5($password);
		
		// Verify that user is in database
		$q = "SELECT password FROM users WHERE username = '$username'";
		$result = mysql_query($q, $connection);
		if(!$result || (mysql_numrows($result) < 1)) {
			return 1; //Indicates username failure
		}      
		 
		// Retrieve password from result
		$dbarray = mysql_fetch_array($result);
		
		// Validate that password is correct
		if($password == $dbarray['password']) {
			return 0; //Success! Username and password confirmed
		}
		else {
			return 1; //Indicates password failure
		}
	}
?>